if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_delete_find')
          and type in ('P','PC'))
   drop procedure bd_proc_schedule_delete_find
go


create procedure bd_proc_schedule_delete_find (@user_serial_str varchar(max),@dept_serial_str varchar(max),@begin_year_month varchar(200),@end_year_month varchar(200),@search_name varchar(200),@ip varchar(200),@operator varchar(200)) as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY
		
			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)
			
			CREATE TABLE #dept_serial(
				id INT IDENTITY,
				dept_serial VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)
			
			CREATE TABLE #del_base(
				id int identity,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				year_month varchar(50)	COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #del_base_info(
				id int identity,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				year_month varchar(50) COLLATE Chinese_PRC_CI_AS,
				year_month_day varchar(50) COLLATE Chinese_PRC_CI_AS
			)

		-- 开启事务
			BEGIN TRAN
				

				IF isnull(@user_serial_str,'') <>'' BEGIN
					insert into #user_serial
					select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');
				END


				IF isnull(@dept_serial_str,'') <> '' BEGIN
					insert into #dept_serial
					select * from string_split(iif(isnull(@dept_serial_str,'')='','',@dept_serial_str),',');
				END

				
				declare @sql varchar(max);

				set @sql = '
					insert into #del_base(user_serial,user_name,year_month) 
					select a.user_serial,a.user_name,a.rq
					from view_bd_schedule a '
				;

				-- 只要用户编号不为空 就拼接关联用户
				IF isnull(@user_serial_str,'') <> '' BEGIN
				
					set @sql += 'inner join #user_serial b on a.user_serial = b.user_serial '
				END ELSE IF isnull(@dept_serial_str,'') <> '' BEGIN
					
					set @sql += 'inner join #dept_serial b on a.dept_serial = b.dept_serial '
				END
				

				-- 这个添件是必填的（前端验证了）
				set @sql += 'where (rq >= '''+convert(varchar(7),@begin_year_month,120)+''' and rq <= '''+convert(varchar(7),@end_year_month,120)+''' )'


				IF isnull(@search_name,'') <> '' and isnull(@search_name,'') <> '' BEGIN
					set @sql += 'and a.user_name like ''%'+@search_name+'%'' or a.user_no like ''%'+@search_name+'%''';
				END

				print @sql;


				-- 执行拼接SQL
				exec(@sql);


				-- 将基础数据升级为全数据(多了year_month_day字段)
				insert into #del_base_info(user_serial,user_name,year_month,year_month_day)
				select a.user_serial,a.user_name,a.year_month,b.date
				from #del_base a
				inner join bd_sys_dim_date b on a.year_month = CONVERT(varchar(7),b.date,120)
				

				-- 添加考勤日志
				insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,gly_no,sj)  	
				select user_serial,year_month_day,1,6,2,0,0,@operator,getdate()
				from #del_base_info
				
				-- 删除排班明细
				delete a
				from bd_kq_schedule_detail a
				inner join #del_base_info b on a.user_serial = b.user_serial and a.shift_date = b.year_month_day

				-- 删除排班表数据
				delete a
				from kt_paiba a
				inner join #del_base_info b on a.user_serial = b.user_serial and a.rq = b.year_month

				set @result = @@ROWCOUNT;

				-- 记录删除排班日志
				insert into wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select distinct 13,0,@operator+'删除'+user_name+year_month+'排班',@operator,getdate(),'',@ip,''
				from #del_base_info


		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	drop table #dept_serial;
	drop table #user_serial;
	drop table #del_base;
	drop table #del_base_info;

	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
go
